package testJDBC;

import com.mysql.cj.jdbc.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.Scanner;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User: 刘远望
 * Date: 2025-05-15
 * Time: 18:20
 */
public class Demo02_DateSource {
    public static void main(String[] args) {
        //定义MySql数据源对象
        MysqlDataSource mysqlDataSource = new MysqlDataSource();
        //设置数据库连接串
        mysqlDataSource.setURL("jdbc:mysql://127.0.0.1:3306/java113?characterEncodng=utf8&alLowPublicKeyRetrieval=true&useSSL=false");
        //用户名
        mysqlDataSource.setUser("root");
        //密码
        mysqlDataSource.setPassword("liuyvanwang");

        //定义jdbc的数据源对象
        DataSource dataSource = mysqlDataSource;

        //定义连接对象
        Connection connection = null;
        //定义预处理sql执行对象
        PreparedStatement preparedStatement = null;
        //定义结果集对象
        ResultSet resultSet = null;

        try {
            //1.通过数据库远来获取数据库连接
            connection = dataSource.getConnection();
            //定义要执行的sql
            String sql = "select student_id, sn, name, mail, class_id from student where name = ?";
            //2.获取预处理sql执行对象
            preparedStatement = connection.prepareStatement(sql);
            //接受用户输入
            System.out.println("请输入学生姓名：");
            Scanner scanner = new Scanner(System.in);
            String name = scanner.next();

            //3.用真是值替换占位符
            preparedStatement.setString(1, name);
            //4.执行sql，获取结果集
            resultSet = preparedStatement.executeQuery();
            //5.遍历结果集
            while (resultSet.next()) {
                //获取学生的各个字段
                long Id = resultSet.getLong(1);
                String Sn = resultSet.getString(2);
                String Name = resultSet.getString(3);
                String Mail = resultSet.getString(4);
                long ClassId = resultSet.getLong(5);
                System.out.println(MessageFormat.format("学生编号 = {0}, 学号 = {1}, 姓名 = {2}, 邮箱 = {3}, 班级 = {4}"
                        , Id, Sn, Name, Mail, ClassId));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //从后往前依次释放资源
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
